package com.web;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.data.HibernateSqlService;

@Controller
public class ChartController {

	@Autowired
	private HibernateSqlService sqlSvc;
	
	private String sql="select substring(time_,1,2) t,"+
		"sum(case substring(cn,1,1) when 'C' then (qnt*sprice) end) [C],"+
		"sum(case substring(cn,1,3) when 'C01' then (qnt*sprice) end) [C01],"+
		"sum(case substring(cn,1,3) when 'C02' then (qnt*sprice) end) [C02],"+
		"sum(case substring(cn,1,3) when 'C03' then (qnt*sprice) end) [C03],"+
		"sum(case substring(cn,1,3) when 'C04' then (qnt*sprice) end) [C04]"+
		"from  sail.c_xt_salesdtl where date_='09-04-07'"+ 
		"group by substring(time_,1,2)";
	
	@RequestMapping
	public ModelAndView getChartHandler()
	{
		return new ModelAndView("chart","data",sqlSvc.find(sql));
	}
}
